package com.xxl.job.executor.service.impl;

import com.alibaba.fastjson.JSON;
import com.xxl.job.core.biz.model.ReturnT;
import com.xxl.job.core.constants.Constants;
import com.xxl.job.core.executor.XxlJobExecutor;
import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.core.util.DateUtil;
import com.xxl.job.core.util.ListUtils;
import com.xxl.job.core.util.StringUtils;
import com.xxl.job.executor.model.ReportingPortal;
import com.xxl.job.executor.service.ReportingPortalService;
import com.xxl.job.executor.service.business.BaseService;
import org.apache.ibatis.annotations.Mapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

/**
 * 统一门户数据统计
 */
//@Service
public class ReportingPortalServiceImpl extends BaseService implements ReportingPortalService {

    private Logger logger = LoggerFactory.getLogger(ReportingPortalServiceImpl.class);

    //统一门户
    @Resource(name = "irspbdsJdbcTemplate")
    private JdbcTemplate irspbdsJdbcTemplate;

    // 人口库
    @Resource(name = "peopleJdbcTemplate")
    private JdbcTemplate ppJdbcTemplate;

    // 法人库
    @Resource(name = "companyJdbcTemplate")
    private JdbcTemplate cpJdbcTemplate;


    /**
     * 统一统计
     *
     * @return
     */
    @Override
    public ReturnT<String> collection() {
        ReturnT<String> r1 = residentCount();//区域人口（万人）
        ReturnT<String> r2 = nationCount();//民族人数排行Top10
        ReturnT<String> r3 = sexRatioCount();//泉州市男女比例
        ReturnT<String> r4 = floatPersonCount();//流动人口Top5
        ReturnT<String> r5 = companyArealDistribution();//企业区域分布
        ReturnT<String> r6 = companyIndustrialDistribution();//企业行业分布
        ReturnT<String> r7 = companyWorkerCount();//企业职工人数统计
        ReturnT<String> r8 = registeredCapitalCount();//企业注册金额数据统计
        ReturnT<String> r9 = companyDataCount();//2010-2019历年企业数据统计
        if(ReturnT.isFail(r1) || ReturnT.isFail(r2) || ReturnT.isFail(r3) ||
                ReturnT.isFail(r4) || ReturnT.isFail(r5) || ReturnT.isFail(r6) ||
                ReturnT.isFail(r7) || ReturnT.isFail(r8) || ReturnT.isFail(r9)) {
            return ReturnT.FAIL;
        } else {
            return ReturnT.SUCCESS;
        }
    }

    @Override //区域人口（万人）
    public ReturnT<String> residentCount() {
        try {
            String[] area = {"鲤城区","丰泽区","洛江区","泉港区","石狮市","晋江市","南安市","惠安县","安溪县","永春县","德化县","经济开发区","台商投资区"};
            //String[] area = {"鲤城区", "丰泽区"};
            List<Map<String, Object>> list = new ArrayList<>();
            for(String a : area) {
                String query = "select count(*) from table36521 where xzdz like '%"+a+"%' and sircZXBZ='有效'";
                Object object = ppJdbcTemplate.queryForObject(query, Object.class);//查询
                Map<String, Object> map = new HashMap<>();
                map.put("name", a);
                map.put("value", object);
                list.add(map);
            }

            if(ListUtils.isEmpty(list)) return ReturnT.FAIL;

            final ReportingPortal reportingPortal = new ReportingPortal();
            reportingPortal.setUNID(StringUtils.getUUID());
            reportingPortal.setAreaCode("泉州市");
            reportingPortal.setYearCode(String.valueOf(new Date().getYear() + 1900));
            reportingPortal.setDateCode(DateUtil.formatDate(new Date()));
            reportingPortal.setName("区域人口统计");
            reportingPortal.setPublicCode("qyrktj");
            reportingPortal.setMemo("单位（人）");
            reportingPortal.setGroupName("people_message");
            reportingPortal.setGroupNameMemo("人口信息分析");
            reportingPortal.setSecGroupName("area_resident_count");
            reportingPortal.setGroupNameMemo("区域人口统计");
            reportingPortal.setTableHtml(JSON.toJSONString(list));
            reportingPortal.setThreeGroupName("");
            reportingPortal.setThreeGroupNameMemo("");
            reportingPortal.setCreatetime(DateUtil.formatDateTime(new Date()));
            logger.info("区域人口统计：{}", JSON.toJSONString(list));
            execute(reportingPortal);// 添加记录
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 区域人口统计成功.");
            XxlJobLogger.log("结果：{}", JSON.toJSON(list));
        } catch (Exception e) {
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 区域人口统计失败.");
            return ReturnT.FAIL;
        }
        return ReturnT.SUCCESS;
    }

    @Override //民族人数排行Top10
    public ReturnT<String> nationCount() {
        try {
            String query = "select a.* from (select count(*) 'value', concat(mz,'族') 'name' from table36521 where sircZXBZ='有效' group by mz) a order by a.value desc limit 0,10;";
            List<Map<String, Object>> list = ppJdbcTemplate.queryForList(query);
            if(ListUtils.isEmpty(list)) return ReturnT.FAIL;

            logger.info("民族人数排行：{}", JSON.toJSONString(list));

            final ReportingPortal reportingPortal = new ReportingPortal();
            reportingPortal.setUNID(StringUtils.getUUID());
            reportingPortal.setAreaCode("泉州市");
            reportingPortal.setYearCode(String.valueOf(new Date().getYear() + 1900));
            reportingPortal.setDateCode(DateUtil.formatDate(new Date()));
            reportingPortal.setName("民族人数排行");
            reportingPortal.setPublicCode("mzrsph");
            reportingPortal.setMemo("top10");
            reportingPortal.setGroupName("people_message");
            reportingPortal.setGroupNameMemo("人口信息分析");
            reportingPortal.setSecGroupName("nation_count_rank");
            reportingPortal.setGroupNameMemo("民族人数排行");
            reportingPortal.setTableHtml(JSON.toJSONString(list));
            reportingPortal.setThreeGroupName("");
            reportingPortal.setThreeGroupNameMemo("");
            reportingPortal.setCreatetime(DateUtil.formatDateTime(new Date()));

            execute(reportingPortal);
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 民族人数排行统计成功.");
            XxlJobLogger.log("结果：{}", JSON.toJSON(list));
        } catch (Exception e) {
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 民族人数排行统计失败.");
            return ReturnT.FAIL;
        }
        return ReturnT.SUCCESS;
    }

    @Override //泉州市男女比例
    public ReturnT<String> sexRatioCount() {
        try {
            String query = "select a.* from (select count(*) 'value', xb 'name' from table36521 where sircZXBZ='有效' group by xb) a order by a.value desc;";
            List<Map<String, Object>> list = ppJdbcTemplate.queryForList(query);
            if(ListUtils.isEmpty(list)) return ReturnT.FAIL;

            logger.info("泉州市男女比例：{}", JSON.toJSONString(list));

            final ReportingPortal reportingPortal = new ReportingPortal();
            reportingPortal.setUNID(StringUtils.getUUID());
            reportingPortal.setAreaCode("泉州市");
            reportingPortal.setYearCode(String.valueOf(new Date().getYear() + 1900));
            reportingPortal.setDateCode(DateUtil.formatDate(new Date()));
            reportingPortal.setName("泉州市男女比例");
            reportingPortal.setPublicCode("qzsnnbl");
            reportingPortal.setMemo("男女人数");
            reportingPortal.setGroupName("people_message");
            reportingPortal.setGroupNameMemo("人口信息分析");
            reportingPortal.setSecGroupName("sex_ratio");
            reportingPortal.setGroupNameMemo("泉州市男女比例");
            reportingPortal.setTableHtml(JSON.toJSONString(list));
            reportingPortal.setThreeGroupName("");
            reportingPortal.setThreeGroupNameMemo("");
            reportingPortal.setCreatetime(DateUtil.formatDateTime(new Date()));

            execute(reportingPortal);
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 泉州市男女比例统计成功.");
            XxlJobLogger.log("结果：{}", JSON.toJSON(list));
        } catch (Exception e) {
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 泉州市男女比例统计失败.");
            return ReturnT.FAIL;
        }
        return ReturnT.SUCCESS;
    }

    @Override //流动人口Top5
    public ReturnT<String> floatPersonCount() {
        try {
            String[] area = {"鲤城区","丰泽区","洛江区","泉港区","石狮市","晋江市","南安市","惠安县","安溪县","永春县","德化县","经济开发区","台商投资区"};
            //String[] area = {"鲤城区", "丰泽区"};
            List<Map<String, Object>> list = new ArrayList<>();
            for(String a : area) {
                String query = "select count(*) from table34802 where JZDZZ_DZMC like '%"+a+"%' and sircZJZT='有效'";
                Object object = ppJdbcTemplate.queryForObject(query, Object.class);//查询
                Map<String, Object> map = new HashMap<>();
                map.put("name", a);
                map.put("value", object);
                list.add(map);
            }

            if(ListUtils.isEmpty(list)) return ReturnT.FAIL;

            List<Map<String, Object>> list1 = sort(list);

            List<Map<String, Object>> list2 = getTopN(list1, 5);

            logger.info("流动人口统计：{}", JSON.toJSONString(list2));

            final ReportingPortal reportingPortal = new ReportingPortal();
            reportingPortal.setUNID(StringUtils.getUUID());
            reportingPortal.setAreaCode("泉州市");
            reportingPortal.setYearCode(String.valueOf(new Date().getYear() + 1900));
            reportingPortal.setDateCode(DateUtil.formatDate(new Date()));
            reportingPortal.setName("流动人口统计");
            reportingPortal.setPublicCode("ldrktj");
            reportingPortal.setMemo("单位（人）");
            reportingPortal.setGroupName("people_message");
            reportingPortal.setGroupNameMemo("人口信息分析");
            reportingPortal.setSecGroupName("float_person_count");
            reportingPortal.setGroupNameMemo("流动人口统计");
            reportingPortal.setTableHtml(JSON.toJSONString(list2));
            reportingPortal.setThreeGroupName("");
            reportingPortal.setThreeGroupNameMemo("");
            reportingPortal.setCreatetime(DateUtil.formatDateTime(new Date()));

            execute(reportingPortal);// 添加记录
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 流动人口统计成功.");
            XxlJobLogger.log("结果：{}", JSON.toJSON(list2));
        } catch (Exception e) {
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 流动人口统计失败.");
            return ReturnT.FAIL;
        }
        return ReturnT.SUCCESS;
    }

    @Override //企业区域分布
    public ReturnT<String> companyArealDistribution() {
        try {
            String[] area = {"鲤城区","丰泽区","洛江区","泉港区","石狮市","晋江市","南安市","惠安县","安溪县","永春县","德化县","经济开发区","台商投资区"};
            //String[] area = {"鲤城区", "丰泽区"};
            List<Map<String, Object>> list = new ArrayList<>();
            for(String a : area) {
                String query = "select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' " +
                        "and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'"+
                        ") and regaddress like '%"+a+"%';";
                //String query = "select count(*) from frk_qyjbxxb where regaddress like '%"+a+"%' and iscancel='否' and registerstate='确立';";
                Object object = cpJdbcTemplate.queryForObject(query, Object.class);//查询
                Map<String, Object> map = new HashMap<>();
                map.put("name", a);
                map.put("value", object);
                list.add(map);
            }

            if(ListUtils.isEmpty(list)) return ReturnT.FAIL;

            final ReportingPortal reportingPortal = new ReportingPortal();
            reportingPortal.setUNID(StringUtils.getUUID());
            reportingPortal.setAreaCode("泉州市");
            reportingPortal.setYearCode(String.valueOf(new Date().getYear() + 1900));
            reportingPortal.setDateCode(DateUtil.formatDate(new Date()));
            reportingPortal.setName("企业区域分布");
            reportingPortal.setPublicCode("qyqyfb");
            reportingPortal.setMemo("单位（家）");
            reportingPortal.setGroupName("company_message");
            reportingPortal.setGroupNameMemo("法人信息分析");
            reportingPortal.setSecGroupName("company_area_distribution");
            reportingPortal.setGroupNameMemo("企业区域分布");
            reportingPortal.setTableHtml(JSON.toJSONString(list));
            reportingPortal.setThreeGroupName("");
            reportingPortal.setThreeGroupNameMemo("");
            reportingPortal.setCreatetime(DateUtil.formatDateTime(new Date()));
            logger.info("企业区域分布：{}", JSON.toJSONString(list));

            execute(reportingPortal);// 添加记录
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 企业区域分布统计成功.");
            XxlJobLogger.log("结果：{}", JSON.toJSON(list));
        } catch (Exception e) {
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 企业区域分布统计失败.");
            return ReturnT.FAIL;
        }
        return ReturnT.SUCCESS;
    }

    @Override //企业行业分布
    public ReturnT<String> companyIndustrialDistribution() {
        List<Map<String, Object>> list = new ArrayList<>();
        StringBuffer buffer;
        Object object;
        Map<String, Object> map;
        try {
            buffer = new StringBuffer();
            buffer.append("select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' ");
            buffer.append("and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'");
            buffer.append(") and indurstryname in(");
            for(int i=0;i < Constants.CLASS1.length-1;i++) {
                buffer.append("'"+Constants.CLASS1[i] + "',");
            }
            buffer.append("'"+Constants.CLASS1[Constants.CLASS1.length-1] + "'");
            buffer.append(");");
            object = cpJdbcTemplate.queryForObject(buffer.toString(), Object.class);//查询
            map = new HashMap<>();
            map.put("name", "农、林、渔、牧业");
            map.put("value", object);
            list.add(map);

            buffer = new StringBuffer();
            buffer.append("select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' ");
            buffer.append("and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'");
            buffer.append(") and indurstryname in(");
            for(int i=0;i < Constants.CLASS2.length-1;i++) {
                buffer.append("'"+Constants.CLASS2[i] + "',");
            }
            buffer.append("'"+Constants.CLASS2[Constants.CLASS2.length-1] + "'");
            buffer.append(");");
            object = cpJdbcTemplate.queryForObject(buffer.toString(), Object.class);//查询
            map = new HashMap<>();
            map.put("name", "生产、加工、制造业");
            map.put("value", object);
            list.add(map);

            buffer = new StringBuffer();
            buffer.append("select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' ");
            buffer.append("and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'");
            buffer.append(") and indurstryname in(");
            for(int i=0;i < Constants.CLASS3.length-1;i++) {
                buffer.append("'"+Constants.CLASS3[i] + "',");
            }
            buffer.append("'"+Constants.CLASS3[Constants.CLASS3.length-1] + "'");
            buffer.append(");");
            object = cpJdbcTemplate.queryForObject(buffer.toString(), Object.class);//查询
            map = new HashMap<>();
            map.put("name", "建筑业");
            map.put("value", object);
            list.add(map);

            buffer = new StringBuffer();
            buffer.append("select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' ");
            buffer.append("and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'");
            buffer.append(") and indurstryname in(");
            for(int i=0;i < Constants.CLASS4.length-1;i++) {
                buffer.append("'"+Constants.CLASS4[i] + "',");
            }
            buffer.append("'"+Constants.CLASS4[Constants.CLASS4.length-1] + "'");
            buffer.append(");");
            object = cpJdbcTemplate.queryForObject(buffer.toString(), Object.class);//查询
            map = new HashMap<>();
            map.put("name", "服务业");
            map.put("value", object);
            list.add(map);

            buffer = new StringBuffer();
            buffer.append("select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' ");
            buffer.append("and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'");
            buffer.append(") and indurstryname in(");
            for(int i=0;i < Constants.CLASS5.length-1;i++) {
                buffer.append("'"+Constants.CLASS5[i] + "',");
            }
            buffer.append("'"+Constants.CLASS5[Constants.CLASS5.length-1] + "'");
            buffer.append(");");
            object = cpJdbcTemplate.queryForObject(buffer.toString(), Object.class);//查询
            map = new HashMap<>();
            map.put("name", "批发、零售业");
            map.put("value", object);
            list.add(map);

            buffer = new StringBuffer();
            buffer.append("select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' ");
            buffer.append("and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'");
            buffer.append(") and indurstryname in(");
            for(int i=0;i < Constants.CLASS6.length-1;i++) {
                buffer.append("'"+Constants.CLASS6[i] + "',");
            }
            buffer.append("'"+Constants.CLASS6[Constants.CLASS6.length-1] + "'");
            buffer.append(");");
            object = cpJdbcTemplate.queryForObject(buffer.toString(), Object.class);//查询
            map = new HashMap<>();
            map.put("name", "金融业");
            map.put("value", object);
            list.add(map);

            buffer = new StringBuffer();
            buffer.append("select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' ");
            buffer.append("and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'");
            buffer.append(") and indurstryname in(");
            for(int i=0;i < Constants.CLASS7.length-1;i++) {
                buffer.append("'"+Constants.CLASS7[i] + "',");
            }
            buffer.append("'"+Constants.CLASS7[Constants.CLASS7.length-1] + "'");
            buffer.append(");");
            object = cpJdbcTemplate.queryForObject(buffer.toString(), Object.class);//查询
            map = new HashMap<>();
            map.put("name", "IT、通信、电子、互联网");
            map.put("value", object);
            list.add(map);

            /*buffer = new StringBuffer();
            buffer.append("select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' ");
            buffer.append("and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'");
            buffer.append(") and indurstryname in(");
            for(int i=0;i < Constants.CLASS8.length-1;i++) {
                buffer.append("'"+Constants.CLASS8[i] + "',");
            }
            buffer.append("'"+Constants.CLASS8[Constants.CLASS8.length-1] + "'");
            buffer.append(");");
            object = cpJdbcTemplate.queryForObject(buffer.toString(), Object.class);//查询
            map = new HashMap<>();
            map.put("name", "交通、运输、物流、仓储");
            map.put("value", object);
            list.add(map);*/

            /*buffer = new StringBuffer();
            buffer.append("select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' ");
            buffer.append("and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'");
            buffer.append(") and indurstryname in(");
            for(int i=0;i < Constants.CLASS9.length-1;i++) {
                buffer.append("'"+Constants.CLASS9[i] + "',");
            }
            buffer.append("'"+Constants.CLASS9[Constants.CLASS9.length-1] + "'");
            buffer.append(");");
            object = cpJdbcTemplate.queryForObject(buffer.toString(), Object.class);//查询
            map = new HashMap<>();
            map.put("name", "能源、矿产、环保");
            map.put("value", object);
            list.add(map);*/

            /*buffer = new StringBuffer();
            buffer.append("select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' ");
            buffer.append("and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'");
            buffer.append(") and indurstryname in(");
            for(int i=0;i < Constants.CLASS10.length-1;i++) {
                buffer.append("'"+Constants.CLASS10[i] + "',");
            }
            buffer.append("'"+Constants.CLASS10[Constants.CLASS10.length-1] + "'");
            buffer.append(");");
            object = cpJdbcTemplate.queryForObject(buffer.toString(), Object.class);//查询
            map = new HashMap<>();
            map.put("name", "文体教育、工艺美术");
            map.put("value", object);
            list.add(map);*/

            /*buffer = new StringBuffer();
            buffer.append("select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' ");
            buffer.append("and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'");
            buffer.append(") and indurstryname in(");
            for(int i=0;i < Constants.CLASS11.length-1;i++) {
                buffer.append("'"+Constants.CLASS11[i] + "',");
            }
            buffer.append("'"+Constants.CLASS11[Constants.CLASS11.length-1] + "'");
            buffer.append(");");
            object = cpJdbcTemplate.queryForObject(buffer.toString(), Object.class);//查询
            map = new HashMap<>();
            map.put("name", "商业服务");
            map.put("value", object);
            list.add(map);*/

            /*buffer = new StringBuffer();
            buffer.append("select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' ");
            buffer.append("and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'");
            buffer.append(") and indurstryname in(");
            for(int i=0;i < Constants.CLASS12.length-1;i++) {
                buffer.append("'"+Constants.CLASS12[i] + "',");
            }
            buffer.append("'"+Constants.CLASS12[Constants.CLASS12.length-1] + "'");
            buffer.append(");");
            object = cpJdbcTemplate.queryForObject(buffer.toString(), Object.class);//查询
            map = new HashMap<>();
            map.put("name", "文化、传媒、娱乐、体育");
            map.put("value", object);
            list.add(map);*/

            if(ListUtils.isEmpty(list)) return ReturnT.FAIL;

            dataProcess(list);//数据处理：计算百分比

            final ReportingPortal reportingPortal = new ReportingPortal();
            reportingPortal.setUNID(StringUtils.getUUID());
            reportingPortal.setAreaCode("泉州市");
            reportingPortal.setYearCode(String.valueOf(new Date().getYear() + 1900));
            reportingPortal.setDateCode(DateUtil.formatDate(new Date()));
            reportingPortal.setName("企业行业分布");
            reportingPortal.setPublicCode("qyhyfb");
            reportingPortal.setMemo("单位（家）");
            reportingPortal.setGroupName("company_message");
            reportingPortal.setGroupNameMemo("法人信息分析");
            reportingPortal.setSecGroupName("company_industry_distribution");
            reportingPortal.setGroupNameMemo("企业行业分布");
            reportingPortal.setTableHtml(JSON.toJSONString(list));
            reportingPortal.setThreeGroupName("");
            reportingPortal.setThreeGroupNameMemo("");
            reportingPortal.setCreatetime(DateUtil.formatDateTime(new Date()));
            logger.info("企业行业分布：{}", JSON.toJSONString(list));

            execute(reportingPortal);// 添加记录
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 企业行业分布统计成功.");
            XxlJobLogger.log("结果：{}", JSON.toJSON(list));
        } catch (Exception e) {
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 企业行业分布统计失败.");
            return ReturnT.FAIL;
        }
        return ReturnT.SUCCESS;
    }

    /**
     * 企业行业分布
     * 数据处理：计算百分比
     * @param list
     */
    private void dataProcess(List<Map<String, Object>> list) {
        if(ListUtils.isEmpty(list)) return;
        long count = 0;
        for(Map<String, Object> map : list) {
            if(null != map && null != map.get("value")) {
                try {
                    long cnt = (long) map.get("value");
                    count = count + cnt;
                } catch (Exception e) {
                    continue;
                }
            }
        }
        if(count == 0) return;
        for(Map<String, Object> map : list) {
            if(null != map && null != map.get("value")) {
                try {
                    long cnt = (long) map.get("value");
                    float floatValue = (float) cnt*100/(float) count;
                    String stringValue = String.valueOf(floatValue);
                    double rate = new BigDecimal(stringValue).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                    //String rate = String.format("%.2f", cnt*100/count);
                    map.put("value1", rate);
                } catch (Exception e) {
                    map.put("value1", 0.0);
                    continue;
                }
            }
        }
    }

    public static void main(String[] args) {
        StringBuffer buffer;
        buffer = new StringBuffer();
        buffer.append("select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' ");
        buffer.append("and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'");
        buffer.append(") and indurstryname in(");
        for(int i=0;i < Constants.CLASS1.length-1;i++) {
            buffer.append("'"+Constants.CLASS1[i] + "',");
        }
        buffer.append("'"+Constants.CLASS1[Constants.CLASS1.length-1] + "'");
        buffer.append(")");
        System.out.println(buffer);
    }

    @Override //企业职工人数统计
    public ReturnT<String> companyWorkerCount() {
        List<Map<String, Object>> list = new ArrayList<>();
        Map<String, Object> map;
        String query;
        Object object;
        try {
            query = "select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' " +
                    "and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'"+
                    ") and workernum >= 10 and workernum < 50;";
            object = cpJdbcTemplate.queryForObject(query, Object.class);//查询
            map = new HashMap<>();
            map.put("name", "10-50");
            map.put("value", object);
            list.add(map);

            query = "select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' " +
                    "and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'"+
                    ") and workernum >= 50 and workernum < 100;";
            object = cpJdbcTemplate.queryForObject(query, Object.class);//查询
            map = new HashMap<>();
            map.put("name", "50-100");
            map.put("value", object);
            list.add(map);

            query = "select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' " +
                    "and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'"+
                    ") and workernum >= 100 and workernum < 500;";
            object = cpJdbcTemplate.queryForObject(query, Object.class);//查询
            map = new HashMap<>();
            map.put("name", "100-500");
            map.put("value", object);
            list.add(map);

            query = "select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' " +
                    "and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'"+
                    ") and workernum >= 500 and workernum < 1000;";
            object = cpJdbcTemplate.queryForObject(query, Object.class);//查询
            map = new HashMap<>();
            map.put("name", "500-1000");
            map.put("value", object);
            list.add(map);

            query = "select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' " +
                    "and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立'"+
                    ") and workernum >= 1000;";
            object = cpJdbcTemplate.queryForObject(query, Object.class);//查询
            map = new HashMap<>();
            map.put("name", "1000及以上");
            map.put("value", object);
            list.add(map);

            if (ListUtils.isEmpty(list)) return ReturnT.FAIL;

            final ReportingPortal reportingPortal = new ReportingPortal();
            reportingPortal.setUNID(StringUtils.getUUID());
            reportingPortal.setAreaCode("泉州市");
            reportingPortal.setYearCode(String.valueOf(new Date().getYear() + 1900));
            reportingPortal.setDateCode(DateUtil.formatDate(new Date()));
            reportingPortal.setName("企业职工人数数据统计");
            reportingPortal.setPublicCode("qyzgrssjtj");
            reportingPortal.setMemo("单位（家）");
            reportingPortal.setGroupName("company_message");
            reportingPortal.setGroupNameMemo("法人信息分析");
            reportingPortal.setSecGroupName("company_worker_count");
            reportingPortal.setGroupNameMemo("企业职工人数数据统计");
            reportingPortal.setTableHtml(JSON.toJSONString(list));
            reportingPortal.setThreeGroupName("");
            reportingPortal.setThreeGroupNameMemo("");
            reportingPortal.setCreatetime(DateUtil.formatDateTime(new Date()));
            logger.info("企业职工人数数据统计：{}", JSON.toJSONString(list));

            execute(reportingPortal);// 添加记录
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 企业职工人数数据统计成功.");
            XxlJobLogger.log("结果：{}", JSON.toJSON(list));
        } catch (Exception e) {
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 企业职工人数数据统计失败.");
            return ReturnT.FAIL;
        }
        return ReturnT.SUCCESS;
    }

    @Override //企业注册金额数据统计
    public ReturnT<String> registeredCapitalCount() {
        List<Map<String, Object>> list = new ArrayList<>();
        Map<String, Object> map;
        String query;
        Object object;
        try {
            //query = "select count(*) from frk_qyjbxxb where (regfundamount >= 10 and regfundamount < 100 and iscancel='否' and registerstate='确立' and sirc_action='add' and regfundacurrency='人民币') or (regfundamount*7 >= 10 and regfundamount*7 < 100 and iscancel='否' and registerstate='确立' and sirc_action='add' and regfundacurrency='美元');";
            query = "select count(*) from frk_qyjbxxb where ((regfundamount >= 10 and regfundamount < 100 and iscancel='否' and registerstate='确立' and sirc_action='add' and regfundacurrency='人民币') "+
                    "or (regfundamount*7 >= 10 and regfundamount*7 < 100 and iscancel='否' and registerstate='确立' and sirc_action='add' and regfundacurrency='美元')) "+
                    "and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立');";
            object = cpJdbcTemplate.queryForObject(query, Object.class);//查询
            map = new HashMap<>();
            map.put("name", "100W");
            map.put("value", object);
            list.add(map);

            query = "select count(*) from frk_qyjbxxb where ((regfundamount >= 100 and regfundamount < 500 and iscancel='否' and registerstate='确立' and sirc_action='add' and regfundacurrency='人民币') "+
                    "or (regfundamount*7 >= 100 and regfundamount*7 < 500 and iscancel='否' and registerstate='确立' and sirc_action='add' and regfundacurrency='美元')) "+
                    "and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立');";
            object = cpJdbcTemplate.queryForObject(query, Object.class);//查询
            map = new HashMap<>();
            map.put("name", "500W");
            map.put("value", object);
            list.add(map);

            query = "select count(*) from frk_qyjbxxb where ((regfundamount >= 500 and regfundamount < 1000 and iscancel='否' and registerstate='确立' and sirc_action='add' and regfundacurrency='人民币') "+
                    "or (regfundamount*7 >= 500 and regfundamount*7 < 1000 and iscancel='否' and registerstate='确立' and sirc_action='add' and regfundacurrency='美元')) "+
                    "and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立');";
            object = cpJdbcTemplate.queryForObject(query, Object.class);//查询
            map = new HashMap<>();
            map.put("name", "1000W");
            map.put("value", object);
            list.add(map);

            query = "select count(*) from frk_qyjbxxb where ((regfundamount >= 1000 and regfundamount < 3000 and iscancel='否' and registerstate='确立' and sirc_action='add' and regfundacurrency='人民币') "+
                    "or (regfundamount*7 >= 1000 and regfundamount*7 < 3000 and iscancel='否' and registerstate='确立' and sirc_action='add' and regfundacurrency='美元')) "+
                    "and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立');";
            object = cpJdbcTemplate.queryForObject(query, Object.class);//查询
            map = new HashMap<>();
            map.put("name", "3000W");
            map.put("value", object);
            list.add(map);

            query = "select count(*) from frk_qyjbxxb where ((regfundamount >= 3000 and iscancel='否' and registerstate='确立' and sirc_action='add' and regfundacurrency='人民币') "+
                    "or (regfundamount*7 >= 3000 and iscancel='否' and registerstate='确立' and sirc_action='add' and regfundacurrency='美元')) "+
                    "and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' and registerstate='确立');";
            object = cpJdbcTemplate.queryForObject(query, Object.class);//查询
            map = new HashMap<>();
            map.put("name", "3000W及以上");
            map.put("value", object);
            list.add(map);

            if (ListUtils.isEmpty(list)) return ReturnT.FAIL;

            final ReportingPortal reportingPortal = new ReportingPortal();
            reportingPortal.setUNID(StringUtils.getUUID());
            reportingPortal.setAreaCode("泉州市");
            reportingPortal.setYearCode(String.valueOf(new Date().getYear() + 1900));
            reportingPortal.setDateCode(DateUtil.formatDate(new Date()));
            reportingPortal.setName("企业注册金额数据统计");
            reportingPortal.setPublicCode("qyzcjesjtj");
            reportingPortal.setMemo("单位（家）");
            reportingPortal.setGroupName("company_message");
            reportingPortal.setGroupNameMemo("法人信息分析");
            reportingPortal.setSecGroupName("company_capital_count");
            reportingPortal.setGroupNameMemo("企业注册金额数据统计");
            reportingPortal.setTableHtml(JSON.toJSONString(list));
            reportingPortal.setThreeGroupName("");
            reportingPortal.setThreeGroupNameMemo("");
            reportingPortal.setCreatetime(DateUtil.formatDateTime(new Date()));
            logger.info("企业注册金额数据统计：{}", JSON.toJSONString(list));

            execute(reportingPortal);// 添加记录
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 企业注册金额数据统计成功.");
            XxlJobLogger.log("结果：{}", JSON.toJSON(list));
        } catch (Exception e) {
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 企业注册金额数据统计失败.");
            return ReturnT.FAIL;
        }
        return ReturnT.SUCCESS;
    }

    @Override //2010-2019历年企业数据统计
    public ReturnT<String> companyDataCount() {
        Map<String, Object> maps = new HashMap<>();
        List<Map<String, Object>> list = new ArrayList<>();
        Map<String, Object> map;
        Object object;
        try {
            for(int i = 2009; i <= 2019; i++) {
                object = companyNumCount(String.valueOf(i));
                maps.put(String.valueOf(i), object);
            }
            for(int i = 2009; i < 2019; i++) {
                map = new HashMap<>();
                int currentYear = i;
                int nextYear = i+1;
                long currentYear_CompanyNum = (long) maps.get(String.valueOf(currentYear));
                long nextYear_CompanyNum = (long) maps.get(String.valueOf(nextYear));

                map = new HashMap<>();
                map.put("name", String.valueOf(nextYear));
                map.put("value1", nextYear_CompanyNum);
                map.put("value2", (nextYear_CompanyNum-currentYear_CompanyNum)*100/currentYear_CompanyNum);
                map.put("value3", nextYear_CompanyNum-currentYear_CompanyNum);
                list.add(map);
            }

            if (ListUtils.isEmpty(list)) return ReturnT.FAIL;

            final ReportingPortal reportingPortal = new ReportingPortal();
            reportingPortal.setUNID(StringUtils.getUUID());
            reportingPortal.setAreaCode("泉州市");
            reportingPortal.setYearCode(String.valueOf(new Date().getYear() + 1900));
            reportingPortal.setDateCode(DateUtil.formatDate(new Date()));
            reportingPortal.setName("历年企业数据统计");
            reportingPortal.setPublicCode("qyzcjesjtj");
            reportingPortal.setMemo("name年份value1年企业总量value2增长率value3年增长量");
            reportingPortal.setGroupName("company_message");
            reportingPortal.setGroupNameMemo("法人信息分析");
            reportingPortal.setSecGroupName("company_data_count");
            reportingPortal.setGroupNameMemo("历年企业数据统计");
            reportingPortal.setTableHtml(JSON.toJSONString(list));
            reportingPortal.setThreeGroupName("");
            reportingPortal.setThreeGroupNameMemo("");
            reportingPortal.setCreatetime(DateUtil.formatDateTime(new Date()));
            logger.info("2010-2019历年企业数据统计：{}", JSON.toJSONString(list));

            execute(reportingPortal);// 添加记录
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 2010-2019历年企业数据统计成功.");
            XxlJobLogger.log("结果：{}", JSON.toJSON(list));
        } catch (Exception e) {
            XxlJobLogger.log("XXL-JOB, IrspbdsJobHandler 2010-2019历年企业数据统计失败.");
            return ReturnT.FAIL;
        }
        return ReturnT.SUCCESS;
    }


    // 2009年至2019年每年企业总量
    private Object companyNumCount(String year) {
        if(StringUtils.isBlank(year)) return 0;
        String query = "select count(*) from frk_qyjbxxb where sirc_action='add' and iscancel='否' and registerstate='确立' "+
                "and cnname not in(select cnname from frk_qyjbxxb where sirc_action='update' and iscancel='是' "+
                "and registerstate='确立' and IFNULL(canceldate,'')<>'' and year(canceldate) <= "+year+
                ") and IFNULL(establishdate,'')<>'' and year(establishdate) <= "+year+";";
        Object object = cpJdbcTemplate.queryForObject(query, Object.class);//查询
        return object;
    }

    /**
     * 是否存在记录
     * @param rp
     * @return
     */
    private boolean exists(ReportingPortal rp) {
        boolean result = false;
        if(rp == null) return result;
        if(StringUtils.isBlank(rp.getGroupName())) return result;
        if(StringUtils.isBlank(rp.getSecGroupName())) return result;
        if(StringUtils.isBlank(rp.getPublicCode())) return result;
        String query = "select count(*) from reporting_portal where groupName='"+rp.getGroupName()
                +"' and secGroupName='"+rp.getSecGroupName()+"' and PublicCode='"+rp.getPublicCode()+"'";
        Object object = irspbdsJdbcTemplate.queryForObject(query, Object.class);//查询
        if(object == null) return result;
        long count = (long) object;
        if(count > 0) result = true;
        return result;
    }

    /**
     * 删除旧记录
     * @param rp
     */
    private void del(ReportingPortal rp) {
        if(StringUtils.isBlank(rp.getGroupName())) return;
        if(StringUtils.isBlank(rp.getSecGroupName())) return;
        if(StringUtils.isBlank(rp.getPublicCode())) return;
        String query = "delete from reporting_portal where groupName='"+rp.getGroupName()
                +"' and secGroupName='"+rp.getSecGroupName()+"' and PublicCode='"+rp.getPublicCode()+"'";
        irspbdsJdbcTemplate.execute(query);
    }

    /**
     * 更新记录
     * @param rp
     */
    private void update(final ReportingPortal rp) {
        if(StringUtils.isBlank(rp.getGroupName())) return;
        if(StringUtils.isBlank(rp.getSecGroupName())) return;
        if(StringUtils.isBlank(rp.getPublicCode())) return;
        StringBuffer buffer = new StringBuffer();
        buffer.append("update reporting_portal set `TableHtml`=?,`createtime`=? ");
        buffer.append("where `groupName`=? and `secGroupName`=? and `PublicCode`=?");
        int count = irspbdsJdbcTemplate.update(buffer.toString(), new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, rp.getTableHtml());
                ps.setString(2, rp.getCreatetime());
                ps.setString(3, rp.getGroupName());
                ps.setString(4, rp.getSecGroupName());
                ps.setString(5, rp.getPublicCode());
            }
        });
    }

    /**
     * 构建sql语句
     * @param rp 对象
     * @return
     */
    private String buildSQL(ReportingPortal rp) {
        if(rp == null) return "";
        if(StringUtils.isBlank(rp.getUNID())) {//主键不能为空
            rp.setUNID(StringUtils.getUUID());
        }
        StringBuffer buffer = new StringBuffer();

        buffer.append("insert into reporting_portal(`UNID`,`AreaCode`,`YearCode`,`DateCode`,`Name`,");
        buffer.append("`PublicCode`,`TableHtml`,`groupName`,`groupNameMemo`,`secGroupName`,");
        buffer.append("`secGroupNameMemo`,`threeGroupName`,`threeGroupNameMemo`,`createtime`,`memo`) ");
        //buffer.append("values(`?`,`?`,`?`,`?`,`?`,`?`,`?`,`?`,`?`,`?`,`?`,`?`,`?`,`?`,`?`)");
        buffer.append("values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

        return buffer.toString();
    }

    /**
     * 添加统计记录
     * @param rp 对象
     */
    private void execute(final ReportingPortal rp) throws Exception {
        if(rp == null) return;
        //exists(rp);
        del(rp);
        String sql = buildSQL(rp);
        irspbdsJdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, rp.getUNID());
                ps.setString(2, rp.getAreaCode());
                ps.setString(3, rp.getYearCode());
                ps.setString(4, rp.getDateCode());
                ps.setString(5, rp.getName());
                ps.setString(6, rp.getPublicCode());
                ps.setString(7, rp.getTableHtml());
                ps.setString(8, rp.getGroupName());
                ps.setString(9, rp.getGroupNameMemo());
                ps.setString(10, rp.getSecGroupName());
                ps.setString(11, rp.getSecGroupNameMemo());
                ps.setString(12, rp.getThreeGroupName());
                ps.setString(13, rp.getThreeGroupNameMemo());
                ps.setString(14, rp.getCreatetime());
                ps.setString(15, rp.getMemo());
            }
        });

    }


}
